<?php
/**
 * NOTICE OF LICENSE
 *
 * This source file is subject to the  Academic Free License (AFL 3.0)
 * that is available through the world-wide-web at this URL:
 * http://opensource.org/licenses/afl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to support@zprofess.com so we can send you a copy immediately.
 *
 * @category   	Prestashop module
 * @package		Review & Rating Synthesis
 * @version 	1.0.2
 * @author 		Rukan <rukanhitokiri@zprofess.com>
 * @copyright   Copyright (c) 2012 ZPROFESS. (http://www.zprofess.com)
 * @license    	http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
 */
require_once (_PS_CLASS_DIR_.'Product.php');

class ZProductReview extends ObjectModel
{
	public		$id;
	public		$id_product;
	public		$id_customer;
	public		$id_guest;
	public		$customer_name;
	public		$email;
	public		$website;
	public		$title;
	public		$content;
	public		$rating;
	public		$status;
	public		$date_add;
	public		$reply_employees;
	public		$name_employees;
	
	protected	$fieldsRequired = array('id_product', 'id_customer', 'content');
	protected	$fieldsSize = array('content' => 65535);
	protected	$fieldsValidate = array('content' => 'isMessage',
										'rating' => 'isFloat');

	protected 	$table = 'zproduct_review';
	protected 	$identifier = 'id_zproduct_review';
	
	const STATUS_CANCEL			= 0;
	const STATUS_PENDING 		= 1;
    const STATUS_ACCEPT			= 2;


	/**
	 * Prepare fields for ObjectModel class (add, update)
	 * All fields are verified (pSQL, intval...)
	 * @view ObjectModel::getFields()
	 *
	 * @return array All object fields
	 */
	public	function getFields()
	{
	 	parent::validateFields(false);
		$fields['id_product'] 		= (int)($this->id_product);
		$fields['id_customer'] 		= (int)($this->id_customer);
		$fields['id_guest'] 		= (int)($this->id_guest);
		$fields['customer_name'] 	= pSQL($this->customer_name);
		$fields['email'] 			= $this->email;
		$fields['website'] 			= pSQL($this->website);
		$fields['title'] 			= pSQL($this->title);
		$fields['content'] 			= pSQL($this->content);
		$fields['rating'] 			= (float)($this->rating);
		$fields['status'] 			= (int)($this->status);
		$fields['date_add'] 		= pSQL($this->date_add);
		$fields['reply_employees'] 		= pSQL($this->reply_employees);
		$fields['name_employees'] 		= pSQL($this->name_employees);
		
		return $fields;
	}
	
	public function __construct($id=NULL,$id_lang=NULL){
		parent::__construct ($id,$id_lang);
	}
	
	/*********************
	 * BackOffice & Grid *
	 *********************/
	
	/**
	 * Return All Status Types
	 * 
	 * @return array All Status Types
	 */
	static public function getStatusTypes()
	{
		return array(self::STATUS_CANCEL 		=> 'Cancel', 
					 self::STATUS_PENDING 		=> 'Pending', 
					 self::STATUS_ACCEPT 		=> 'Accept');
	}
	
	/**
	 * Return Status Types
	 * 
     * @param int $status
	 * @return string 	status Type
	 */
	static public function displayStatusType($status)
	{
		$options = self::getStatusTypes();
    	return $options[$status];
	}
	
	/**
	 * Return rating for each criterion by review 
	 * 
	 * @param int $id_review	current review id
	 * @return array			rating for each criterion
	 */
	public static function getRatingByReview($id_review)
	{
		return Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
		SELECT *
		  FROM `'._DB_PREFIX_.'zproduct_rating` zr
		WHERE zr.`id_zproduct_review` = '.(int)$id_review
		);
		
	}
	
	/**
	 * return name product
	 * 
	 * @param	int 		$id_product	
	 * @return 	string		name product
	 */
	public static function getNameProductById($id_product)
	{
		global $cookie;
		
		return Db::getInstance(_PS_USE_SQL_SLAVE_)->getValue('
		SELECT pl.`name`
		FROM `'._DB_PREFIX_.'product_lang` pl
		WHERE pl.`id_lang` = '.(int)$cookie->id_lang.' 
		AND pl.id_product = '.(int)$id_product
		);
	}
	
	/***************
	 * FrontOffice *
	 ***************/
	
	/**
	 * Get all rating by product
	 * 
	 * @param int $id_product	current product id
	 * @param int $id_lang		current language id	
	 * @return array 			rating result
	 */
	private static function _getRatingByProduct($id_product, $id_lang)
	{
		if (!Validate::isUnsignedId($id_product) || !Validate::isUnsignedId($id_lang))
			die(Tools::displayError());
		$validate = Configuration::get('ZPRO_VALIDATED_BY_EMPLOYEE');
		
		return (Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
		SELECT zre.`id_zproduct_review`, zra.`rating`, zcl.`name`, zra.`id_zproduct_criterion`
		FROM `'._DB_PREFIX_.'zproduct_review` zre
		LEFT JOIN `'._DB_PREFIX_.'zproduct_rating` zra ON (zra.`id_zproduct_review` = zre.`id_zproduct_review`)
		LEFT JOIN `'._DB_PREFIX_.'zproduct_criterion` zc ON (zc.`id_zproduct_criterion` = zra.`id_zproduct_criterion`)
		LEFT JOIN `'._DB_PREFIX_.'zproduct_criterion_lang` zcl ON (zcl.`id_zproduct_criterion` = zra.`id_zproduct_criterion`)
		WHERE zre.`id_product` = '.(int)($id_product).'
		AND zcl.`id_lang` = '.(int)($id_lang).
		($validate ? ' AND zre.`status` = 2' : '')));
	}
	
	/**
	 * Return number of review and average rating by product
	 * 
	 * @param int $id_product	current product id
	 * @return array 			number review by product
	 */
	public static function getReviewNumber($id_product)
	{
		if (!Validate::isUnsignedId($id_product))
			die(Tools::displayError());
		$validate = (int)(Configuration::get('ZPRO_VALIDATED_BY_EMPLOYEE'));

		$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->getRow('
		SELECT COUNT(zr.`id_product`) AS nbr
		FROM `'._DB_PREFIX_.'zproduct_review` zr
		WHERE `id_product` = '.(int)$id_product.($validate ? ' AND `status` = 2' : ''));
		return (int)$result['nbr'];
	}
	
	/**
	 * Get only one or all product review by customer
	 * Get one product comment to calculation last time review
	 * 
	 * @param int 		$id_product   	current product id
	 * @param int 		$id_customer	current customer id if exists
	 * @param int 		$id_guest		current guest id if exists
	 * @param boolean 	$last			return only a record or all record
	 * @return array 					product comment result
	 */
	public static function getReviewByCustomer($id_product, $id_customer, $id_guest = false, $last = false)
	{
		if (!Validate::isUnsignedId($id_product) || !Validate::isUnsignedId($id_customer))
			die(Tools::displayError());
		$results = Db::getInstance()->ExecuteS('
		SELECT * 
		FROM `'._DB_PREFIX_.'zproduct_review` zr
		WHERE zr.`id_product` = '.(int)($id_product).' AND '.($id_guest ? 'zr.`id_guest` = '.(int)$id_guest : 'zr.`id_customer` = '.(int)$id_customer).'
		ORDER BY zr.`date_add` DESC '.($last ? 'LIMIT 1' : ''));
		
		if ($last)
			return array_shift($results);
		
		return $results;
	}
	
	/**
	 * Add grade to a criterion
	 *
     * @param int 		$id_zproduct_criterion
	 * @param int 		$rating						
	 * @return boolean
	 */
	public function addGrade($id_zproduct_criterion, $rating)
	{
		if (!Validate::isUnsignedId($id_zproduct_criterion))
			die(Tools::displayError());
		if ($rating < 0)
			$rating = 0;
		elseif ($rating > 20)
			$rating = 20;
		$row = array('id_zproduct_review' => (int)$this->id, 
					 'id_zproduct_criterion' => (int)$id_zproduct_criterion,
					 'rating' => (int)($rating));
		return Db::getInstance()->AutoExecute(_DB_PREFIX_.'zproduct_rating', $row, 'INSERT');
	}
	
	/**
	 * edit grade to a criterion
	 *
     * @param int 		$id_zproduct_criterion
	 * @param int 		$rating						
	 * @return boolean
	 */
	public function editGrade($id_zproduct_criterion, $rating)
	{
		if (!Validate::isUnsignedId($id_zproduct_criterion))
			die(Tools::displayError());
		if ($rating < 0)
			$rating = 0;
		elseif ($rating > 20)
			$rating = 20;
		
		
			
		if ($this->testIsSetRating($id_zproduct_criterion)!=0){
			return Db::getInstance(_PS_USE_SQL_SLAVE_)->Execute('
				UPDATE `'._DB_PREFIX_.'zproduct_rating` SET `rating`='.$rating.' WHERE (`id_zproduct_review`='.$this->id.') AND (`id_zproduct_criterion`='.$id_zproduct_criterion.')'
			  );
		}
		else  {
			return $this->addGrade($id_zproduct_criterion, $rating);
		}
	}
	
	/**
	 * test is set id and rating
	 *
     * @param int 		$id_zproduct_criterion
	 * @param int 		$rating						
	 * @return boolean
	 */
	public function testIsSetRating ($id_zproduct_criterion) {
		$rs =  Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
			SELECT * FROM '._DB_PREFIX_.'zproduct_rating WHERE id_zproduct_review='.$this->id.' and id_zproduct_criterion='.$id_zproduct_criterion
		  );
		 return sizeof($rs);
	}
	
	/**
	 * Get reviews by IdProduct
	 *
	 * @return array Comments
	 */
	public static function getReviewByProduct($id_product, $p = 1, $n = null)
	{
		if (!Validate::isUnsignedId($id_product))
			die(Tools::displayError());
		$validate = Configuration::get('ZPRO_VALIDATED_BY_EMPLOYEE');
		$p = (int)$p;
		$n = (int)$n;
		if ($p <= 1)
			$p = 1;
		if ($n != null AND $n <= 0)
			$n = 5;
		$rows = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
		SELECT *
		  FROM `'._DB_PREFIX_.'zproduct_review` zre
		  LEFT JOIN `'._DB_PREFIX_.'zalias_custommers` zc
			ON zre.id_customer = zc.id_custommers
		WHERE zre.`id_product` = '.(int)$id_product.($validate == '1' ? ' AND zre.`status` = 2' : '').'
		ORDER BY zre.`date_add` DESC
		'.($n ? 'LIMIT '.(int)(($p - 1) * $n).', '.(int)$n : ''));
		
		foreach ($rows as $key=>$row) {
			if (empty($row['vlike'])) {
				$rows[$key]['vlike'] = 0;
			} else {
				$vlikeArray = explode(",", trim($row['vlike'], ","));
				$rows[$key]['vlike'] = count($vlikeArray);
			}

			if (empty($row['vdislike'])) {
				$rows[$key]['vdislike'] = 0;
			} else {
				$vdislikeArray = explode(",", trim($row['vlike'], ","));
				$rows[$key]['vdislike'] = count($vdislikeArray);
			}	
			
			//alias
			if($row['active'] == 2)
				$rows[$key]['customer_name'] = $row['alias'];
			
			$rows[$key]['ratings'] = ZProductReview::_getRatingByReview($row['id_zproduct_review']);
		}
		return $rows;
	}
	
	/**
	 * Return rating for $review
	 */
	
	public static function _getRatingByReview ($id_review) {
		$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
		SELECT `id_zproduct_criterion`,`rating` FROM '._DB_PREFIX_.'zproduct_rating
		WHERE id_zproduct_review='.$id_review);
		
		$rs = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('SELECT id_zproduct_criterion FROM '._DB_PREFIX_.'zproduct_criterion');
		$rs1= array ();
		
		foreach ($rs as $value) {
			$rs1[$value['id_zproduct_criterion']] = 0;
			foreach ($result as $value2) {
				if ($value2['id_zproduct_criterion']  == (int)$value['id_zproduct_criterion'])
					$rs1[$value['id_zproduct_criterion']] = (int)$value2['rating'];
			}
		} 
		
		return $rs1;
	}
	
	/**
	 * Return averages rating for each criterion by product 
	 * 
	 * @param int $id_product	current product id
	 * @param int $id_lang		current language id	
	 * @return array			averages rating for each criterion
	 */
	public static function getAveragesByProduct($id_product, $id_lang = null, $averageTotalFlag = false)
	{
		if (!$id_lang) {
			global $cookie;
			$id_lang = $cookie->id_lang;
		}
		$ratings = ZProductReview::_getRatingByProduct((int)$id_product, (int)$id_lang);
		$total = ZProductReview::getReviewNumber((int)$id_product);

		if (!sizeof($ratings) OR !$total) {
			if ($averageTotalFlag)
				return 0;
			return array();
			
		}
			
		
		// Addition ratings for each criterion
		$criterionsRatingTotal = array();
		for ($i = 0; $i < count($ratings); $i++) {
			if (array_key_exists($ratings[$i]['id_zproduct_criterion'], $criterionsRatingTotal) === false)
				$criterionsRatingTotal[$ratings[$i]['id_zproduct_criterion']] = (int)$ratings[$i]['rating'];
			else
				$criterionsRatingTotal[$ratings[$i]['id_zproduct_criterion']] += (int)$ratings[$i]['rating'];
		}

		/* Finally compute the averages */
		$averages = array();
		foreach ($criterionsRatingTotal AS $key => $criterionRatingTotal)
			$averages[(int)$key] = (int)$total ? ((int)$criterionRatingTotal / (int)$total) : 0;
		
		if ($averageTotalFlag) {
			$averageTotal = 0;
			foreach ($averages AS $average)
				$averageTotal += (float)($average);
			$averageTotal = count($averages) ? ($averageTotal / count($averages)) : 0;
			return $averageTotal = round($averageTotal, 1);
		} else 
			return $averages;
	}
	
	/**
	 * Get latest reviews by IdProduct
	 *
	 * @param int $number		latest reviews		
	 * @return array Comments
	 */
	public static function getLatestReviews()
	{
		$validate = Configuration::get('ZPRO_VALIDATED_BY_EMPLOYEE');
		$number = Configuration::get('ZPRO_NUMBER_LATEST_REVIEWS');
		
		$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
		SELECT zc.*, id_product, content, rating, customer_name, email, title, website, date_add
		  FROM `'._DB_PREFIX_.'zproduct_review` zre
			LEFT JOIN `'._DB_PREFIX_.'zalias_custommers` zc
			ON zre.id_customer = zc.id_custommers
		'.($validate == '1' ? ' WHERE zre.`status` = 2' : '').'
		ORDER BY zre.`date_add` DESC LIMIT 0,'.$number);
		
		foreach ($result as $key=>$row) {
			//alias
			if($row['active'] == 2)
				$result[$key]['customer_name'] = $row['alias'];
		}
		return $result;
	}
	
	public static function getLikeMost()
	{
		$validate = Configuration::get('ZPRO_VALIDATED_BY_EMPLOYEE');
		$number = Configuration::get('ZPRO_NUMBER_VOTE_MOST');
		
		$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS('
		SELECT zc.*, id_product, content, rating, customer_name, email, title, website, date_add, number_like
		  FROM `'._DB_PREFIX_.'zproduct_review` zre
			LEFT JOIN `'._DB_PREFIX_.'zalias_custommers` zc
			ON zre.id_customer = zc.id_custommers
		'.($validate == '1' ? ' WHERE zre.`status` = 2' : '').'
		ORDER BY zre.`number_like` DESC LIMIT 0,'.$number);
		
		foreach ($result as $key=>$row) {
			//alias
			if($row['active'] == 2)
				$result[$key]['customer_name'] = $row['alias'];
		}
		return $result;
	}

        public static function getReviewsByCustomer($id_customer, $p, $n, $orderBy, $orderWay , $id_lang){
            $validate = (int)(Configuration::get('ZPRO_VALIDATED_BY_EMPLOYEE'));
            $p = (int)$p;
		$n = (int)$n;
		if ($p <= 1)
			$p = 1;
		if ($n != null AND $n <= 0)
			$n = 5;
            $sql = '
		SELECT zre.`vlike`, zre.`vdislike`,zre.`id_zproduct_review` , zre.`id_product`, zre.`content`, zre.`rating`, zre.`customer_name`, zre.`email`, zre.`title`, zre.`website`, zre.`date_add`, zre.`number_like`, pl.`name`, pl.`link_rewrite`, i.`id_image`, il.`legend`, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 AS new
                FROM `'._DB_PREFIX_.'zproduct_review` zre
                LEFT JOIN `'._DB_PREFIX_.'product` p ON zre.`id_product` = p.`id_product`
                LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')
                LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
                LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)($id_lang).')
		WHERE zre.`id_customer` = '.(int)$id_customer.($validate ? ' AND `status` = 2' : '').' AND p.`active` = 1'
                .($orderBy ? ' ORDER BY zre.`'.  pSQL($orderBy).'` '.  pSQL($orderWay):'')
                .($n ? ' LIMIT '.(int)(($p - 1) * $n).', '.(int)$n : '');

            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
            
	        foreach ($result as $key=>$row) {
				if (empty($row['vlike'])) {
					$result[$key]['vlike'] = 0;
				} else {
					$vlikeArray = explode(",", trim($row['vlike'], ","));
					$result[$key]['vlike'] = count($vlikeArray);
				}
	
				if (empty($row['vdislike'])) {
					$result[$key]['vdislike'] = 0;
				} else {
					$vdislikeArray = explode(",", trim($row['vlike'], ","));
					$result[$key]['vdislike'] = count($vdislikeArray);
				}	
				
			}
            
            return $result;
        }

        public static function getReviewNumberByCustomer($id_customer){
            $sql = '
                SELECT COUNT(zre.`id_zproduct_review`) AS rn
                FROM `'._DB_PREFIX_.'zproduct_review` zre
                WHERE zre.`id_customer` = '.(int)$id_customer;
            $result = Db::getInstance(_PS_USE_SQL_SLAVE_)->ExecuteS($sql);
            return (int)$result[0]['rn'];
        }
}
